/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package apus.persistence.jdbc;

import apus.entity.Subscriber;
import apus.entity.User;
import apus.exception.DatabaseException;
import apus.persistence.UserDAO;
import apus.persistence.proxy.OrganizationProxy;
import apus.persistence.proxy.PersonProxy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author 21vek
 */
public class JdbcUserDAO extends AbstractJdbcDAO<User> implements UserDAO {

    @Override
    public User read(int id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void update(User entity) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<User> findAll() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void create(User entity) {
        entity.setId(getNextUser());

        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            // subscriber
            if (entity.getSubscriber() != null) {
                st = cn.prepareStatement("INSERT INTO USERS (ID_USER, ROLE_ID, LOGIN, PASSWORD, SUBSCRIBER_ID) VALUES (?, ?, ?, ?, ?);");
                st.setInt(5, entity.getSubscriber().getId());
            } else { //employee
                st = cn.prepareStatement("INSERT INTO USERS (ID_USER, ROLE_ID, LOGIN, PASSWORD) VALUES (?, ?, ?, ?);");
            }

            st.setInt(1, entity.getId());
            st.setInt(2, entity.getRole());
            st.setString(3, entity.getLogin());
            st.setString(4, entity.getPassword());

            st.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Can't create new user", e);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public void delete(User entity) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("delete from USERS where ID_USER=?;");
            st.setInt(1, entity.getId());
            st.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Can't delete user with ID=" + entity.getId(), e);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public void changePassword(String login, String newPassword) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("UPDATE USERS set PASSWORD=? where LOGIN=?;");
            st.setString(1, newPassword);
            st.setString(2, login);
            st.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Can't change password of " + login, e);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public int checkAuthorisation(String login, String password) {
        Connection connection = getConnection();
        Statement st = null;
        ResultSet rs = null;

        try {
            st = connection.createStatement();
            rs = st.executeQuery("SELECT * FROM USERS WHERE LOGIN='" + login + "' AND PASSWORD='" + password + "';");

            if (rs.next()) {
                return rs.getInt("ROLE_ID");
            }
        } catch (SQLException ex) {
            throw new DatabaseException("Can't check authorisation", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        return -1;
    }

    @Override
    public boolean isActionAllowed(User user, String action) {
        Connection connection = getConnection();
        Statement st = null;
        ResultSet rs = null;

        try {
            st = connection.createStatement();
            rs = st.executeQuery("SELECT * FROM ROLE_ACTION"
                    + " WHERE ROLE_ID=(SELECT ROLE_ID FROM USERS WHERE LOGIN='" + user.getLogin() + "')"
                    + " AND ACTION_ID=(SELECT ID_ACTION FROM ACTIONS WHERE NAME_ACTION='" + action + "');");

            if (rs.next()) {
                return true;
            }
        } catch (SQLException ex) {
            throw new DatabaseException("Can't check authorisation", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        return false;
    }

    @Override
    public String getSubscriberName(User user) {

        Connection cn = getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = cn.prepareStatement("SELECT SUBSCRIBER_NAME FROM SUBSCRIBER "
                    + "JOIN USERS ON SUBSCRIBER_ID=ID_SUBSCRIBER AND LOGIN=?;");
            /*
             * + "WHERE ID_SUBSCRIBER=(SELECT SUBSCRIBER_ID" + " FROM USERS
             * WHERE ID_USER=?)");
             */
            st.setString(1, user.getLogin());
            rs = st.executeQuery();
            rs.next();
            return rs.getString("SUBSCRIBER_NAME").trim();

        } catch (SQLException e) {
            throw new DatabaseException("Can't read subscriber name by user", e);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public List<User> findAllPersonal() {

        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        User employee;
        List<User> employeeList = new ArrayList<>();
        try {
            st = cn.createStatement();

            // admins + ..
            rs = st.executeQuery("SELECT ID_USER, ROLE_ID, LOGIN FROM USERS "
                    + "WHERE ROLE_ID=1;");

            while (rs.next()) {
                employee = new User(rs.getInt("ID_USER"));
                employee.setRole(rs.getInt("ROLE_ID"));
                employee.setLogin(rs.getString("LOGIN").trim());
                employeeList.add(employee);
            }

            // .. + cashiers
            rs = st.executeQuery("SELECT ID_USER, ROLE_ID, LOGIN FROM USERS "
                    + "WHERE ROLE_ID=2;");

            while (rs.next()) {
                employee = new User(rs.getInt("ID_USER"));
                employee.setRole(rs.getInt("ROLE_ID"));
                employee.setLogin(rs.getString("LOGIN").trim());
                employeeList.add(employee);
            }

        } catch (SQLException e) {
            throw new DatabaseException("Can't read employee list", e);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        return employeeList;
    }

    @Override
    public User findByLogin(String login) {
        login=login.trim();
        Connection cn = getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        int tp;
        User employee=null;
        try {
            st = cn.prepareStatement("SELECT ID_USER, SUBSCRIBER_ID, ROLE_ID, PASSWORD FROM USERS "
                    + "WHERE LOGIN=?;");
            st.setString(1, login);
            rs = st.executeQuery();

            if (rs.next()) {
                st = cn.prepareStatement("SELECT SUBSCRIBER_TYPE FROM SUBSCRIBER "
                        + "WHERE ID_SUBSCRIBER=?;");
                st.setInt(1, rs.getInt("SUBSCRIBER_ID"));

                rs2 = st.executeQuery();
                if (rs2.next()) {
                    tp = rs2.getInt("SUBSCRIBER_TYPE");
                    employee = new User(rs.getInt("ID_USER"));
                    Subscriber sub;
                    
                    if (tp == 0) {
                        sub = new PersonProxy();
                        sub.setId(rs.getInt("SUBSCRIBER_ID"));
                    } else {
                        sub = new OrganizationProxy();
                        sub.setId(rs.getInt("SUBSCRIBER_ID"));
                    }
                    
                    employee.setSubscriber(sub);
                    employee.setRole(rs.getInt("ROLE_ID"));
                    employee.setLogin(login);
                    employee.setPassword(rs.getString("PASSWORD"));
                }
            }
        } catch (SQLException e) {
            throw new DatabaseException("Can't read employee with login " + login, e);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (rs2 != null) {
                    rs2.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        return employee;
    }
}
